﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Analysis.DAL.Repositories
{
    public class BaseDataRepository
    {
        public static string GenerateInsertDynamic(List<KeyValuePair<string, object>> data, List<string> parameters, List<object> values)
        {
            string colList = "";
            string valList = "";

            colList = " ( ";
            valList = " values( ";

            for (int i = 0; i < data.Count; ++i)
            {
                string nameP = "p" + i.ToString();
                colList += string.Format("[{0}]", data[i].Key);
                valList += "@" + nameP;
                parameters.Add("@" + nameP);

                if (i != data.Count - 1)
                {
                    colList += ", ";
                    valList += ", ";
                }
                else
                {
                    colList += ") ";
                    valList += ") ";
                }

                if (data[i].Value == null)
                    values.Add(DBNull.Value);
                else
                    values.Add(data[i].Value);
            }

            return colList + valList;
        }

        public static string GenerateUpdateDynamic(List<KeyValuePair<string, object>> data, List<string> parameters, List<object> values)
        {
            string strSQL = "";

            for (int i = 0; i < data.Count; ++i)
            {
                string nameP = "p" + i.ToString();
                strSQL += string.Format("[{0}] = ", data[i].Key);
                strSQL += "@" + nameP;
                parameters.Add("@" + nameP);

                if (i != data.Count - 1)
                    strSQL += ", ";

                if (data[i].Value == null)
                    values.Add(DBNull.Value);
                else
                    values.Add(data[i].Value);
            }

            return strSQL;
        }

        public static string GetUserParam(Guid userId, string paramName)
        {
            string result = "";
            DataTable dt = DataAccess.GetDataTable("select ParamValue from UserParam (nolock) where UserId = @UserId and ParamName = @ParamName", new string[] { "UserId", "ParamName" }, new object[] { userId, paramName });
            if (dt.Rows.Count > 0)
                result = dt.Rows[0].IsNull("ParamValue") ? "" : dt.Rows[0]["ParamValue"].ToString();
            return result;
        }

        public static void SetUserParam(Guid userId, string paramName, string paramValue)
        {
            DataAccess.ExecuteProcedure("spUserParamEdit",
                new string[] { "UserId", "ParamName", "ParamValue" }, new object[] { userId, paramName, paramValue });
        }

        public static string CustomColumnEdit(string tableName, string columnNameOld, string columnName)
        {
            List<SqlParameter> outP = new List<SqlParameter>();
            SqlParameter p = new SqlParameter("MsgErr", SqlDbType.VarChar, 100);
            p.Direction = ParameterDirection.Output;
            outP.Add(p);

            DataAccess.ExecuteProcedure("spCustomColumn_Edit", new string[] {"@TableName", "@CustomColumnNameOld", "@CustomColumnName" }, new object[] { tableName, columnNameOld, columnName }, outP.ToArray());
            return (string)outP[0].Value;
        }

        public static void CustomColumnDelete(string tableName, string columnName)
        {
            DataAccess.ExecuteProcedure("spCustomColumn_Delete", new string[] { "@TableName", "@ColumnName" }, new object[] { tableName, columnName });
        }

        public static void CustomColumnAdd(string tableName)
        {
            DataAccess.ExecuteProcedure("spCustomColumn_Add", new string[] { "@TableName" }, new object[] { tableName });
        }

        public static DataTable MonthCombo()
        {
            return DataAccess.GetDataTable("select MonthId, MonthName from Months (nolock) order by MonthId");
        }

        public static int MonthValid(string monthName)
        {
            DataTable dt = DataAccess.GetDataTable("select MonthId, MonthName from Months (nolock) where MonthName = @MonthName", new string[] { "@MonthName" }, new object[] { monthName } );
            if (dt.Rows.Count > 0)
                return Convert.ToInt32(dt.Rows[0]["MonthId"]);
            else
                return -1;

        }
    }
}
